import yaml
import pandas as pd
from sqlalchemy import create_engine as cg


def load_config():
    with open("config.yaml", 'r', encoding='utf-8') as f:
        data = yaml.load(f.read(), Loader=yaml.FullLoader)
    return data


def get_tjd_engine(config):
    mysql_args = config['mysql']
    user = mysql_args['user']
    ip = mysql_args['ip']
    data_base = mysql_args['database']
    password = mysql_args['password']
    engine = cg(f'mysql+pymysql://{user}:{password}@{ip}:3306/{data_base}?charset=utf8')
    return engine


if __name__ == "__main__":
    # 获取配置参数
    config = load_config()
    # 获取交易所
    exchange = config['exchange'].keys()
    print('交易所代码', exchange)
    # 获取每个交易所品种
    for k, v in config['exchange'].items():
        print("交易所", k, "期货品种", v.keys())
    # 获取mysql引擎
    my_engine = get_tjd_engine(config=config)
    # 期货数据库中有哪些主力合约
    codes = pd.read_sql("show tables", my_engine).iloc[:, 0].to_list()
    # 获取每个主力合约的高开低收数据
    for code in codes:
        data = pd.read_sql(f"select * from {code}", my_engine)
        print(code, data.shape)
    # 获取腾景分类期货数据一级指标和所属关系（字典）
    tjd_l1 = config['tjd_category']
    # 获取腾景一级分类
    print(tjd_l1.keys())
    # 获取二级分类
    for l1_name, l2_li in tjd_l1.items():
        print("L1:", l1_name, "包含 L2:", l2_li)
    # 直接获取所有品种
    all_category = {}
    for exchange, exchange_codes in config['exchange'].items():
        all_category = {**all_category, **exchange_codes}
    for character_name, future_code in all_category.items():
        data = pd.read_sql(f"select * from {future_code[1]}", my_engine)
        print("中文名称", character_name, "future_code", future_code[0], 'mysql名', future_code[1], data['trade_date'][0])

    # 根据腾景二级分类获取日度K线数据
    # for l1_name, l2_li in tjd_l1.items():
    #     print("L1 行业", l1_name)
    #     for l2_name in l2_li:
    #         future_code = all_category[l2_name]
    #         print(future_code)



